#include "databasemanager.h"
#include <QFile>
#include <QTextStream>
#include <QSqlRecord>
#include <QVariant>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
#include <QDebug>
#include <QDir>

DatabaseManager::DatabaseManager(QObject *parent)
    : QObject(parent)
{
}

DatabaseManager::~DatabaseManager()
{
    disconnectFromDatabase();
}

bool DatabaseManager::connectToDatabase(const QString &hostname, const QString &databaseName, 
                                       const QString &username, const QString &password)
{
    // 如果已有连接，先断开
    if (m_database.isOpen()) {
        m_database.close();
    }
    
    // 使用SQL Server ODBC连接
    m_database = QSqlDatabase::addDatabase("QODBC");
    
    // 构建SQL Server连接字符串
    QString connectionString;
    if (username.isEmpty() && password.isEmpty()) {
        // 使用Windows身份验证
        connectionString = QString("DRIVER={ODBC Driver 17 for SQL Server};"
                                 "SERVER=%1;"
                                 "DATABASE=%2;"
                                 "Trusted_Connection=yes;")
                                 .arg(hostname)
                                 .arg(databaseName);
    } else {
        // 使用SQL Server身份验证
        connectionString = QString("DRIVER={ODBC Driver 17 for SQL Server};"
                                 "SERVER=%1;"
                                 "DATABASE=%2;"
                                 "UID=%3;"
                                 "PWD=%4;"
                                 "Trusted_Connection=no;")
                                 .arg(hostname)
                                 .arg(databaseName)
                                 .arg(username)
                                 .arg(password);
    }
    
    m_database.setDatabaseName(connectionString);
    
    // 尝试连接数据库
    if (!m_database.open()) {
        m_lastError = m_database.lastError().text();
        qDebug() << "SQL Server连接失败:" << m_lastError;
        qDebug() << "连接字符串:" << connectionString;
        return false;
    }
    
    qDebug() << "SQL Server连接成功！";
    qDebug() << "服务器:" << hostname << "数据库:" << databaseName;
    
    // 创建表
    if (!createTables()) {
        m_lastError = "创建数据库表失败";
        return false;
    }
    
    return true;
}

void DatabaseManager::disconnectFromDatabase()
{
    if (m_database.isOpen()) {
        m_database.close();
    }
}

bool DatabaseManager::openDatabase()
{
    // 使用SQL Server数据库 - 连接到本地默认实例
    return connectToDatabase("localhost", "AnimalExpertSystem", "", "");
}

bool DatabaseManager::isConnected() const
{
    return m_database.isOpen();
}

bool DatabaseManager::createTables()
{
    if (!m_database.isOpen()) {
        m_lastError = "数据库未连接";
        return false;
    }
    
    QSqlQuery query(m_database);
    
    // 创建特征表 (SQL Server语法)
    QString createFeaturesTable = R"(
        IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='features' AND xtype='U')
        CREATE TABLE features (
            id INT IDENTITY(1,1) PRIMARY KEY,
            name NVARCHAR(255) NOT NULL UNIQUE
        )
    )";
    
    if (!query.exec(createFeaturesTable)) {
        m_lastError = "创建特征表失败: " + query.lastError().text();
        qDebug() << m_lastError;
        return false;
    }
    
    // 创建规则表 (SQL Server语法)
    QString createRulesTable = R"(
        IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='rules' AND xtype='U')
        CREATE TABLE rules (
            id INT IDENTITY(1,1) PRIMARY KEY,
            conditions NVARCHAR(MAX) NOT NULL,
            conclusion NVARCHAR(255) NOT NULL
        )
    )";
    
    if (!query.exec(createRulesTable)) {
        m_lastError = "创建规则表失败: " + query.lastError().text();
        qDebug() << m_lastError;
        return false;
    }
    
    // 添加一些初始数据（如果表为空）
    query.exec("SELECT COUNT(*) FROM features");
    if (query.next() && query.value(0).toInt() == 0) {
        // 添加示例特征
        addFeature("没有腿");
        addFeature("会爬行");
        addFeature("有鳞片");
        addFeature("冷血");
        addFeature("有毛发");
        addFeature("哺乳动物");
        
        // 添加示例规则
        QStringList conditions1;
        conditions1 << "没有腿" << "会爬行";
        addRule(conditions1, "可能是蛇");
        
        QStringList conditions2;
        conditions2 << "有鳞片" << "冷血";
        addRule(conditions2, "可能是爬行动物");
        
        QStringList conditions3;
        conditions3 << "有毛发" << "哺乳动物";
        addRule(conditions3, "可能是哺乳动物");
    }
    
    qDebug() << "数据库表创建成功";
    return true;
}

bool DatabaseManager::addFeature(const QString &feature)
{
    if (!m_database.isOpen()) {
        m_lastError = "数据库未连接";
        return false;
    }
    
    QSqlQuery query(m_database);
    query.prepare("INSERT INTO features (name) VALUES (?)");
    query.addBindValue(feature);
    
    if (!query.exec()) {
        m_lastError = "添加特征失败: " + query.lastError().text();
        qDebug() << m_lastError;
        return false;
    }
    
    return true;
}

QStringList DatabaseManager::getAllFeatures()
{
    QStringList features;
    
    if (!m_database.isOpen()) {
        m_lastError = "数据库未连接";
        return features;
    }
    
    QSqlQuery query(m_database);
    if (!query.exec("SELECT name FROM features ORDER BY id")) {
        m_lastError = "查询特征失败: " + query.lastError().text();
        return features;
    }
    
    while (query.next()) {
        features << query.value(0).toString();
    }
    
    return features;
}

QList<QPair<int, QString>> DatabaseManager::getAllFeaturesWithId()
{
    QList<QPair<int, QString>> features;
    
    if (!m_database.isOpen()) {
        m_lastError = "数据库未连接";
        return features;
    }
    
    QSqlQuery query(m_database);
    if (!query.exec("SELECT id, name FROM features ORDER BY id")) {
        m_lastError = "查询特征失败: " + query.lastError().text();
        return features;
    }
    
    while (query.next()) {
        int id = query.value(0).toInt();
        QString name = query.value(1).toString();
        features.append(qMakePair(id, name));
    }
    
    return features;
}

bool DatabaseManager::clearFeatures()
{
    if (!m_database.isOpen()) {
        m_lastError = "数据库未连接";
        return false;
    }
    
    QSqlQuery query(m_database);
    if (!query.exec("DELETE FROM features")) {
        m_lastError = "清空特征失败: " + query.lastError().text();
        return false;
    }
    
    return true;
}

bool DatabaseManager::updateFeature(int id, const QString &feature)
{
    if (!m_database.isOpen()) {
        m_lastError = "数据库未连接";
        return false;
    }
    
    QSqlQuery query(m_database);
    query.prepare("UPDATE features SET name = ? WHERE id = ?");
    query.addBindValue(feature);
    query.addBindValue(id);
    
    if (!query.exec()) {
        m_lastError = "更新特征失败: " + query.lastError().text();
        return false;
    }
    
    return true;
}

bool DatabaseManager::deleteFeature(int id)
{
    if (!m_database.isOpen()) {
        m_lastError = "数据库未连接";
        return false;
    }
    
    QSqlQuery query(m_database);
    query.prepare("DELETE FROM features WHERE id = ?");
    query.addBindValue(id);
    
    if (!query.exec()) {
        m_lastError = "删除特征失败: " + query.lastError().text();
        return false;
    }
    
    return true;
}

bool DatabaseManager::addRule(const QString &ruleName, const QString &category, 
                            const QStringList &conditions, const QString &conclusion)
{
    if (!m_database.isOpen()) {
        m_lastError = "数据库未连接";
        return false;
    }
    
    // 将条件列表转换为字符串
    QString conditionsStr = conditions.join(" AND ");
    
    QSqlQuery query(m_database);
    query.prepare("INSERT INTO Rules (name, category, conditions, conclusion) VALUES (?, ?, ?, ?)");
    query.addBindValue(ruleName);
    query.addBindValue(category);
    query.addBindValue(conditionsStr);
    query.addBindValue(conclusion);
    
    if (!query.exec()) {
        m_lastError = "添加规则失败: " + query.lastError().text();
        return false;
    }
    
    return true;
}

QList<QStringList> DatabaseManager::getAllRules()
{
    QList<QStringList> rules;
    
    if (!m_database.isOpen()) {
        m_lastError = "数据库未连接";
        return rules;
    }
    
    QSqlQuery query(m_database);
    if (!query.exec("SELECT conditions, conclusion FROM rules ORDER BY id")) {
        m_lastError = "查询规则失败: " + query.lastError().text();
        return rules;
    }
    
    while (query.next()) {
        QStringList rule;
        rule << query.value(0).toString() << query.value(1).toString();
        rules << rule;
    }
    
    return rules;
}

QList<std::tuple<int, QStringList, QString>> DatabaseManager::getAllRulesWithId()
{
    QList<std::tuple<int, QStringList, QString>> rules;
    
    if (!m_database.isOpen()) {
        m_lastError = "数据库未连接";
        return rules;
    }
    
    QSqlQuery query(m_database);
    if (!query.exec("SELECT id, conditions, conclusion FROM rules ORDER BY id")) {
        m_lastError = "查询规则失败: " + query.lastError().text();
        return rules;
    }
    
    while (query.next()) {
        int id = query.value(0).toInt();
        QString conditionsStr = query.value(1).toString();
        QString conclusion = query.value(2).toString();
        
        // 解析条件字符串
        QStringList conditions = conditionsStr.split(" AND ", Qt::SkipEmptyParts);
        
        rules.append(std::make_tuple(id, conditions, conclusion));
    }
    
    return rules;
}

QList<std::tuple<int, QString, QString, QStringList, QString>> DatabaseManager::getAllRulesWithFullInfo()
{
    QList<std::tuple<int, QString, QString, QStringList, QString>> rules;
    
    if (!m_database.isOpen()) {
        m_lastError = "数据库未连接";
        return rules;
    }
    
    QSqlQuery query(m_database);
    if (!query.exec("SELECT id, name, category, conditions, conclusion FROM Rules ORDER BY id")) {
        m_lastError = "查询规则失败: " + query.lastError().text();
        return rules;
    }
    
    while (query.next()) {
        int id = query.value(0).toInt();
        QString name = query.value(1).toString();
        QString category = query.value(2).toString();
        QString conditionsStr = query.value(3).toString();
        QString conclusion = query.value(4).toString();
        
        // 解析条件字符串
        QStringList conditions = conditionsStr.split(" AND ", Qt::SkipEmptyParts);
        
        rules.append(std::make_tuple(id, name, category, conditions, conclusion));
    }
    
    return rules;
}

bool DatabaseManager::addRule(const QStringList &conditions, const QString &conclusion)
{
    if (!m_database.isOpen()) {
        m_lastError = "数据库未连接";
        return false;
    }
    
    // 将条件列表转换为字符串
    QString conditionsStr = conditions.join(" AND ");
    
    // 生成规则名称（基于条件和结论）
    QString ruleName = QString("规则_%1").arg(conditionsStr.left(20));
    QString category = "用户添加规则";
    
    QSqlQuery query(m_database);
    query.prepare("INSERT INTO Rules (name, category, conditions, conclusion) VALUES (?, ?, ?, ?)");
    query.addBindValue(ruleName);
    query.addBindValue(category);
    query.addBindValue(conditionsStr);
    query.addBindValue(conclusion);
    
    if (!query.exec()) {
        m_lastError = "添加规则失败: " + query.lastError().text();
        return false;
    }
    
    return true;
}

bool DatabaseManager::updateRule(int id, const QStringList &conditions, const QString &conclusion)
{
    if (!m_database.isOpen()) {
        m_lastError = "数据库未连接";
        return false;
    }
    
    QString conditionsStr = conditions.join(" AND ");
    
    QSqlQuery query(m_database);
    query.prepare("UPDATE rules SET conditions = ?, conclusion = ? WHERE id = ?");
    query.addBindValue(conditionsStr);
    query.addBindValue(conclusion);
    query.addBindValue(id);
    
    if (!query.exec()) {
        m_lastError = "更新规则失败: " + query.lastError().text();
        return false;
    }
    
    return true;
}

bool DatabaseManager::updateRule(int id, const QString &name, const QString &category, const QStringList &conditions, const QString &conclusion)
{
    if (!m_database.isOpen()) {
        m_lastError = "数据库未连接";
        return false;
    }
    
    QString conditionsStr = conditions.join(" AND ");
    
    QSqlQuery query(m_database);
    query.prepare("UPDATE rules SET name = ?, category = ?, conditions = ?, conclusion = ? WHERE id = ?");
    query.addBindValue(name);
    query.addBindValue(category);
    query.addBindValue(conditionsStr);
    query.addBindValue(conclusion);
    query.addBindValue(id);
    
    if (!query.exec()) {
        m_lastError = "更新规则失败: " + query.lastError().text();
        return false;
    }
    
    return true;
}

bool DatabaseManager::deleteRule(int id)
{
    if (!m_database.isOpen()) {
        m_lastError = "数据库未连接";
        return false;
    }
    
    QSqlQuery query(m_database);
    query.prepare("DELETE FROM rules WHERE id = ?");
    query.addBindValue(id);
    
    if (!query.exec()) {
        m_lastError = "删除规则失败: " + query.lastError().text();
        return false;
    }
    
    return true;
}

bool DatabaseManager::clearRules()
{
    if (!m_database.isOpen()) {
        m_lastError = "数据库未连接";
        return false;
    }
    
    QSqlQuery query(m_database);
    if (!query.exec("DELETE FROM rules")) {
        m_lastError = "清空规则失败: " + query.lastError().text();
        return false;
    }
    
    return true;
}

bool DatabaseManager::importFeaturesFromFile(const QString &filePath)
{
    QFile file(filePath);
    if (!file.open(QIODevice::ReadOnly | QIODevice::Text)) {
        m_lastError = "无法打开文件: " + filePath;
        return false;
    }
    
    QTextStream in(&file);
    while (!in.atEnd()) {
        QString line = in.readLine().trimmed();
        if (!line.isEmpty()) {
            addFeature(line);
        }
    }
    
    return true;
}

bool DatabaseManager::importRulesFromFile(const QString &filePath)
{
    QFile file(filePath);
    if (!file.open(QIODevice::ReadOnly | QIODevice::Text)) {
        m_lastError = "无法打开文件: " + filePath;
        return false;
    }
    
    QTextStream in(&file);
    while (!in.atEnd()) {
        QString line = in.readLine().trimmed();
        if (!line.isEmpty() && line.contains("->")) {
            QStringList parts = line.split("->");
            if (parts.size() == 2) {
                QString conditionsStr = parts[0].trimmed();
                QString conclusion = parts[1].trimmed();
                
                QStringList conditions = conditionsStr.split(",");
                for (int i = 0; i < conditions.size(); ++i) {
                    conditions[i] = conditions[i].trimmed();
                }
                
                addRule(conditions, conclusion);
            }
        }
    }
    
    return true;
}

QString DatabaseManager::lastError() const
{
    return m_lastError;
}